USE [C:\USERS\STEPHANIE HUANG\DESKTOP\WORLDTRADEBANK\APP_DATA\BANK.MDF]
GO

/****** Object:  Table [dbo].[R_APPLICATION]    Script Date: 12/25/2014 15:12:47 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[R_APPLICATION](
	[application_id] [int] IDENTITY(1,1) NOT NULL,
	[policy_no] [nvarchar](50) NULL,
	[first_name] [nvarchar](50) NULL,
	[last_name] [nvarchar](50) NULL,
	[nric] [nvarchar](50) NULL,
	[nationality] [nvarchar](50) NULL,
	[dob] [date] NULL,
	[gender] [nvarchar](50) NULL,
	[smoker] [nvarchar](50) NULL,
	[mailing_address] [nvarchar](50) NULL,
	[postal_code] [nvarchar](50) NULL,
	[mobile_no] [nvarchar](50) NULL,
	[tel_no] [nvarchar](50) NULL,
	[email] [nvarchar](50) NULL,
	[occupation] [nvarchar](50) NULL,
	[job_duties] [nvarchar](50) NULL,
	[employer] [nvarchar](50) NULL,
	[no_of_years] [decimal](4, 2) NULL,
	[industry] [nvarchar](50) NULL,
	[annual_income_current] [decimal](18, 2) NULL,
	[annual_income_previous] [decimal](18, 2) NULL,
	[other_income_source] [nvarchar](50) NULL,
	[other_source_income] [decimal](18, 2) NULL,
	[source_of_wealth] [nvarchar](50) NULL,
	[bankruptcy] [nvarchar](50) NULL,
	[benefit_type] [nvarchar](50) NULL,
	[basic_sum_insured] [decimal](18, 2) NULL,
	[plus_rider_sum_insured] [decimal](18, 2) NULL,
	[waiver_rider_premium] [decimal](18, 2) NULL,
	[total_premium] [decimal](18, 2) NULL,
	[premium_frequency] [nvarchar](50) NULL,
	[payment_method] [nvarchar](50) NULL,
	[auto_rebalancing] [nvarchar](50) NULL,
	[status] [nvarchar](50) NULL,
 CONSTRAINT [PK_R_APPLICATION] PRIMARY KEY CLUSTERED 
(
	[application_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[R_APPLICATION]  WITH CHECK ADD  CONSTRAINT [FK_R_APPLICATION_R_CERTIFICATE] FOREIGN KEY([policy_no])
REFERENCES [dbo].[R_POLICY] ([policy_no])
GO

ALTER TABLE [dbo].[R_APPLICATION] CHECK CONSTRAINT [FK_R_APPLICATION_R_CERTIFICATE]
GO



USE [C:\USERS\STEPHANIE HUANG\DESKTOP\WORLDTRADEBANK\APP_DATA\BANK.MDF]
GO

/****** Object:  Table [dbo].[R_APPLICATION_FUND]    Script Date: 12/25/2014 15:13:23 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[R_APPLICATION_FUND](
	[id] [int] NULL,
	[application_id] [int] NULL,
	[fund_code] [nvarchar](50) NULL,
	[fund_allocation] [decimal](18, 3) NULL,
	[remark] [nvarchar](50) NULL
) ON [PRIMARY]

GO



USE [C:\USERS\STEPHANIE HUANG\DESKTOP\WORLDTRADEBANK\APP_DATA\BANK.MDF]
GO

/****** Object:  Table [dbo].[R_ANNUAL_INSURANCE_CHARGE]    Script Date: 12/25/2014 15:14:09 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[R_ANNUAL_INSURANCE_CHARGE](
	[age] [int] NULL,
	[MN] [decimal](18, 3) NULL,
	[MS] [decimal](18, 3) NULL,
	[FN] [decimal](18, 3) NULL,
	[FS] [decimal](18, 3) NULL
) ON [PRIMARY]

GO



USE [C:\USERS\STEPHANIE HUANG\DESKTOP\WORLDTRADEBANK\APP_DATA\BANK.MDF]
GO

/****** Object:  Table [dbo].[R_ANNUAL_PLUS_RIDER_CHARGE]    Script Date: 12/25/2014 15:14:31 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[R_ANNUAL_PLUS_RIDER_CHARGE](
	[age] [int] NOT NULL,
	[MN] [decimal](18, 3) NULL,
	[MS] [decimal](18, 3) NULL,
	[FN] [decimal](18, 3) NULL,
	[FS] [decimal](18, 3) NULL,
 CONSTRAINT [PK_R_PLUS_RIDER_CHARGE] PRIMARY KEY CLUSTERED 
(
	[age] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO



USE [C:\USERS\STEPHANIE HUANG\DESKTOP\WORLDTRADEBANK\APP_DATA\BANK.MDF]
GO

/****** Object:  Table [dbo].[R_ANNUAL_WAIVER_RIDER_PREMIUM]    Script Date: 12/25/2014 15:15:40 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[R_ANNUAL_WAIVER_RIDER_PREMIUM](
	[age] [int] NOT NULL,
	[MN] [decimal](18, 3) NULL,
	[MS] [decimal](18, 3) NULL,
	[FN] [decimal](18, 3) NULL,
	[FS] [decimal](18, 3) NULL,
 CONSTRAINT [PK_R_ANNUAL_WAIVER_RIDER_PREMIUM] PRIMARY KEY CLUSTERED 
(
	[age] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO



USE [C:\USERS\STEPHANIE HUANG\DESKTOP\WORLDTRADEBANK\APP_DATA\BANK.MDF]
GO

/****** Object:  Table [dbo].[R_CLAIM]    Script Date: 12/25/2014 15:15:53 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[R_CLAIM](
	[claim_id] [int] NULL,
	[policy_no] [nvarchar](50) NULL,
	[claimant_name] [nvarchar](50) NULL,
	[claimant_nric] [nvarchar](50) NULL,
	[claimant_dob] [date] NULL,
	[claimant_relationship] [nvarchar](50) NULL,
	[claim_type] [nvarchar](50) NULL,
	[claim_description] [nvarchar](max) NULL,
	[claim_amount] [decimal](18, 2) NULL,
	[claim_date] [date] NULL
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[R_CLAIM]  WITH CHECK ADD  CONSTRAINT [FK_R_CLAIM_R_CERTIFICATE] FOREIGN KEY([policy_no])
REFERENCES [dbo].[R_POLICY] ([policy_no])
GO

ALTER TABLE [dbo].[R_CLAIM] CHECK CONSTRAINT [FK_R_CLAIM_R_CERTIFICATE]
GO



USE [C:\USERS\STEPHANIE HUANG\DESKTOP\WORLDTRADEBANK\APP_DATA\BANK.MDF]
GO

/****** Object:  Table [dbo].[R_FUND]    Script Date: 12/25/2014 15:16:04 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[R_FUND](
	[fund_code] [nvarchar](50) NOT NULL,
	[fund_name] [nvarchar](50) NULL,
	[description] [nvarchar](max) NULL,
	[management_fee] [decimal](18, 5) NULL,
	[base_random_price] [decimal](18, 3) NULL,
 CONSTRAINT [PK_R_FUND] PRIMARY KEY CLUSTERED 
(
	[fund_code] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


USE [C:\USERS\STEPHANIE HUANG\DESKTOP\WORLDTRADEBANK\APP_DATA\BANK.MDF]
GO

/****** Object:  Table [dbo].[R_FUND_DAILY_PRICE]    Script Date: 12/25/2014 15:16:12 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[R_FUND_DAILY_PRICE](
	[price_id] [int] IDENTITY(1,1) NOT NULL,
	[fund_code] [nvarchar](50) NULL,
	[valuation_date] [date] NULL,
	[bid_price] [numeric](18, 2) NULL,
	[offer_price] [numeric](18, 2) NULL,
	[currency] [nvarchar](50) NULL,
 CONSTRAINT [PK_R_FUND_DAILY_PRICE] PRIMARY KEY CLUSTERED 
(
	[price_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[R_FUND_DAILY_PRICE]  WITH CHECK ADD  CONSTRAINT [FK_R_FUND_DAILY_PRICE_R_FUND] FOREIGN KEY([fund_code])
REFERENCES [dbo].[R_FUND] ([fund_code])
GO

ALTER TABLE [dbo].[R_FUND_DAILY_PRICE] CHECK CONSTRAINT [FK_R_FUND_DAILY_PRICE_R_FUND]
GO



USE [C:\USERS\STEPHANIE HUANG\DESKTOP\WORLDTRADEBANK\APP_DATA\BANK.MDF]
GO

/****** Object:  Table [dbo].[R_FUND_TRANSACTION]    Script Date: 12/25/2014 15:16:27 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[R_FUND_TRANSACTION](
	[policy_no] [nvarchar](50) NOT NULL,
	[fund_code] [nvarchar](50) NOT NULL,
	[trade_time] [datetime] NOT NULL,
	[trade_type] [nchar](50) NULL,
	[unit] [decimal](18, 5) NOT NULL,
	[price] [decimal](18, 5) NOT NULL,
 CONSTRAINT [PK_R_FUND_TRANSACTION] PRIMARY KEY CLUSTERED 
(
	[policy_no] ASC,
	[fund_code] ASC,
	[trade_time] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[R_FUND_TRANSACTION]  WITH CHECK ADD  CONSTRAINT [FK_R_FUND_TRANSACTION_R_CERTIFICATE_FUND] FOREIGN KEY([fund_code], [policy_no])
REFERENCES [dbo].[R_POLICY_FUND] ([fund_code], [policy_no])
GO

ALTER TABLE [dbo].[R_FUND_TRANSACTION] CHECK CONSTRAINT [FK_R_FUND_TRANSACTION_R_CERTIFICATE_FUND]
GO



USE [C:\USERS\STEPHANIE HUANG\DESKTOP\WORLDTRADEBANK\APP_DATA\BANK.MDF]
GO

/****** Object:  Table [dbo].[R_INSURED]    Script Date: 12/25/2014 15:16:36 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[R_INSURED](
	[insured_id] [int] IDENTITY(1,1) NOT NULL,
	[first_name] [nvarchar](50) NULL,
	[last_name] [nvarchar](50) NULL,
	[nric] [nvarchar](50) NULL,
	[nationality] [nvarchar](50) NULL,
	[dob] [date] NULL,
	[gender] [nvarchar](50) NULL,
	[smoker] [nvarchar](50) NULL,
	[mailing_address] [nvarchar](50) NULL,
	[postal_code] [nvarchar](50) NULL,
	[mobile_no] [nvarchar](50) NULL,
	[tel_no] [nvarchar](50) NULL,
	[email] [nvarchar](50) NULL,
	[occupation] [nvarchar](50) NULL,
	[job_duties] [nvarchar](50) NULL,
	[employer] [nvarchar](50) NULL,
	[no_of_years] [decimal](4, 2) NULL,
	[industry] [nvarchar](50) NULL,
	[annual_income_current] [decimal](18, 2) NULL,
	[annual_income_previous] [decimal](18, 2) NULL,
	[other_income_source] [nvarchar](50) NULL,
	[other_source_income] [decimal](18, 2) NULL,
	[source_of_wealth] [nvarchar](50) NULL,
	[bankruptcy] [nvarchar](50) NULL,
 CONSTRAINT [PK_R_INSURED_1] PRIMARY KEY CLUSTERED 
(
	[insured_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO



USE [C:\USERS\STEPHANIE HUANG\DESKTOP\WORLDTRADEBANK\APP_DATA\BANK.MDF]
GO

/****** Object:  Table [dbo].[R_PAYMENT]    Script Date: 12/25/2014 15:16:44 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[R_PAYMENT](
	[policy_no] [nvarchar](50) NOT NULL,
	[payment_datetime] [datetime] NOT NULL,
	[total_charges_amount] [decimal](18, 5) NULL,
	[invest_amount] [decimal](18, 5) NULL,
	[waiver_rider_amount] [decimal](18, 5) NULL,
	[manage_amount] [decimal](18, 5) NULL,
	[transaction_id] [int] NULL,
	[type] [nvarchar](50) NULL,
	[amount] [decimal](18, 2) NULL,
 CONSTRAINT [PK_R_PREMIUM_PAYMENT] PRIMARY KEY CLUSTERED 
(
	[policy_no] ASC,
	[payment_datetime] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[R_PAYMENT]  WITH CHECK ADD  CONSTRAINT [FK_R_PREMIUM_PAYMENT_R_CERTIFICATE] FOREIGN KEY([policy_no])
REFERENCES [dbo].[R_POLICY] ([policy_no])
GO

ALTER TABLE [dbo].[R_PAYMENT] CHECK CONSTRAINT [FK_R_PREMIUM_PAYMENT_R_CERTIFICATE]
GO



USE [C:\USERS\STEPHANIE HUANG\DESKTOP\WORLDTRADEBANK\APP_DATA\BANK.MDF]
GO

/****** Object:  Table [dbo].[R_POLICY]    Script Date: 12/25/2014 15:17:16 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[R_POLICY](
	[policy_no] [nvarchar](50) NOT NULL,
	[application_id] [int] NULL,
	[user_id] [int] NULL,
	[insured_id] [int] NULL,
	[issue_date] [date] NULL,
	[benefit_type] [nvarchar](50) NULL,
	[staff_id] [int] NULL,
	[basic_sum_insured] [decimal](18, 2) NULL,
	[plus_rider_sum_insured] [decimal](18, 2) NULL,
	[waiver_rider_premium] [decimal](18, 2) NULL,
	[total_annual_premium] [decimal](18, 2) NULL,
	[premium_frequency] [nvarchar](50) NULL,
	[payment_method] [nvarchar](50) NULL,
	[status] [nvarchar](50) NULL,
 CONSTRAINT [PK_R_CERTIFICATE] PRIMARY KEY CLUSTERED 
(
	[policy_no] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[R_POLICY]  WITH CHECK ADD  CONSTRAINT [FK_R_CERTIFICATE_R_APPLICATION] FOREIGN KEY([application_id])
REFERENCES [dbo].[R_APPLICATION] ([application_id])
GO

ALTER TABLE [dbo].[R_POLICY] CHECK CONSTRAINT [FK_R_CERTIFICATE_R_APPLICATION]
GO



USE [C:\USERS\STEPHANIE HUANG\DESKTOP\WORLDTRADEBANK\APP_DATA\BANK.MDF]
GO

/****** Object:  Table [dbo].[R_POLICY_FUND]    Script Date: 12/25/2014 15:17:25 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[R_POLICY_FUND](
	[fund_code] [nvarchar](50) NOT NULL,
	[policy_no] [nvarchar](50) NOT NULL,
	[allocation] [decimal](18, 5) NULL,
	[invest_units] [decimal](18, 5) NOT NULL,
	[insurance_charge_units] [decimal](18, 5) NULL,
	[plus_rider_charge_units] [decimal](18, 5) NULL,
	[other_charges_units] [decimal](18, 5) NULL,
	[last_trade_date] [datetime] NOT NULL,
	[status] [nvarchar](50) NULL,
 CONSTRAINT [PK_R_CERTIFICATE_FUND] PRIMARY KEY CLUSTERED 
(
	[fund_code] ASC,
	[policy_no] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[R_POLICY_FUND]  WITH CHECK ADD  CONSTRAINT [FK_R_CERTIFICATE_FUND_R_CERTIFICATE] FOREIGN KEY([policy_no])
REFERENCES [dbo].[R_POLICY] ([policy_no])
GO

ALTER TABLE [dbo].[R_POLICY_FUND] CHECK CONSTRAINT [FK_R_CERTIFICATE_FUND_R_CERTIFICATE]
GO

ALTER TABLE [dbo].[R_POLICY_FUND]  WITH CHECK ADD  CONSTRAINT [FK_R_CERTIFICATE_FUND_R_FUND] FOREIGN KEY([fund_code])
REFERENCES [dbo].[R_FUND] ([fund_code])
GO

ALTER TABLE [dbo].[R_POLICY_FUND] CHECK CONSTRAINT [FK_R_CERTIFICATE_FUND_R_FUND]
GO



USE [C:\USERS\STEPHANIE HUANG\DESKTOP\WORLDTRADEBANK\APP_DATA\BANK.MDF]
GO

/****** Object:  Table [dbo].[R_PREMIUM_INCREASE]    Script Date: 12/25/2014 15:17:35 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[R_PREMIUM_INCREASE](
	[id] [int] NULL,
	[policy_no] [nvarchar](50) NULL,
	[increase_amount] [decimal](18, 2) NULL,
	[datetime] [datetime] NULL
) ON [PRIMARY]

GO

